<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;

class WorksExport implements FromCollection
{
    public function collection()
    {
        $sql = "SELECT
	eo.eo_id as '订单id',eo.created_at as '发单时间',
CASE (eo.status)
WHEN 0 THEN
	'发单中'
WHEN 1 THEN
	'抢单完成'
WHEN 2 THEN
	'抢单结束'
WHEN 3 THEN
	'抢单取消'
WHEN 4 THEN
	'订单结束'
END AS '订单状态',
	shop.shop_id as '店铺id',shop.shop_name as '店铺名称',
	lu.we_id as '发单人id',lu.openid as '发单人openid',lu.nickname as '发单人微信昵称',
	ew.work_no as '工单编号',ew.receive_time as '接单时间',
CASE (ew.status)
WHEN 0 THEN
	'已接单'
WHEN 1 THEN
	'已投稿'
WHEN 2 THEN
	'已验收'
WHEN 3 THEN
	'待结算'
WHEN 4 THEN
	'已结算'
WHEN 5 THEN
	'验收不通过'
WHEN 6 THEN
	'工单取消'
END AS '工单状态',
	es.mch_billno as '红包订单号',es.total_amount as '工单金额',es.payment_amount as '实际红包金额',es.service_fee as '服务费',es.created_at as '结算时间',lu2.we_id as '接单人id',lu2.openid as '接单人openid',lu2.nickname as '接单人微信昵称'
from explore_order as eo
LEFT JOIN shop on eo.shop_id = shop.shop_id
LEFT JOIN lottery_users as lu on eo.we_id = lu.we_id
LEFT JOIN explore_work as ew on eo.eo_id = ew.eo_id
LEFT JOIN lottery_users as lu2 on ew.receiver_we_id = lu2.we_id
LEFT JOIN explore_settlement as es on ew.work_id = es.work_id;";

        $result = DB::select($sql);
        $result = json_decode(json_encode($result),true);
        foreach($result as $key=>$value){
            $result[$key]['工单编号'] = $value['工单编号']."\t";
            $result[$key]['红包订单号'] = $value['红包订单号']."\t";
        }
        array_unshift($result, array_keys($result[0]));
        return collect($result);
    }

}
